﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Threading.Tasks;
using DataTier;
using System.Data;


namespace DataAccessLayer
{
    public class DBBooking
    {

        private static SqlCommand dbCmd = null;

        public static List<Booking> getAllBookings(int sId)
        {


        List<Booking> returnList = new List<Booking>();
  

            string sql = @"select * from Booking where sId = '"+sId+"'";
            //string sql = @"select * from Booking where bookingDate > '" + d + "' AND batteryId IN (select batteryId from Battery where stationId = '" + stationId + "')";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Booking b;

            while (dbReader.Read())
            {
                b = new Booking((int)(dbReader["bookingId"]),(int)(dbReader["sId"]));

                returnList.Add(b);
            }
        
            AccessDBSQLClient.Close();

            foreach (Booking book in returnList)
            {
                book.Subscription = DBSubscription.getSubscription(book.Subscription.SubscriptionId);
                //book.Subscription.Customer = DBCustomer.FindCustomerByNo(book.Subscription.Customer.CustomerId);
            }
            

            return returnList;

        }

        public static void AddBooking(Booking b, Customer c, Subscription s, Route r, DateTime lockedUntil)
        {

            if (r.RouteId == 0)
            {
                r.RouteId = 1;

            }

            var lockedUntil2 = lockedUntil.ToString("yyyy-MM-dd HH:mm:ss");
            var dateTime = b.BookingDate.ToString("yyyy-MM-dd HH:mm:ss");
            string sql = @"insert into Booking (bookingDate, sId, batteryId, isCollected, isCancelled, routeId, lockedUntil) values ('" + dateTime + "','" + s.SubscriptionId + "','" + b.Battery.BatteryId + "','" + b.IsCollected + "','" + b.IsCancelled + "','" + r.RouteId + "','" + lockedUntil2 + "')";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();
        }

        public static List<Booking> FindBookingTimeInteval(DateTime bookingDate, int stationId)
        {
            var d = bookingDate.ToString("yyyy-MM-dd HH:mm:ss");

            List<Booking> returnList = new List<Booking>();


            string sql = @"select * from Booking where bookingDate > '" + d + "' AND batteryId IN (select batteryId from Battery where stationId = '" + stationId + "') Order by bookingDate";


            dbCmd = AccessDBSQLClient.GetDbCommand(sql);


            IDataReader dbReader;
            dbReader = dbCmd.ExecuteReader();

            Booking b;


            while (dbReader.Read())
            {
                //var dateTime = b.BookingDate.Date.ToString("dd-MM-yyyy HH:mm:ss");
                b = new Booking(Convert.ToDateTime(dbReader["bookingDate"]));


                returnList.Add(b);
            }
            dbReader.Close();
            AccessDBSQLClient.Close();
            return returnList;
        }


        public static void BookingSweeper(DateTime dateTime)
        {
            var dateTime2 = dateTime.ToString("yyyy-MM-dd HH:mm");
            string sql = @"delete from Booking where routeId = 1 and lockedUntil < '" + dateTime2 + "'";

           SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();
        }

        public static void cancelBooking(int bookingId, bool cancelled)
        {
            DateTime lockedDate = DateTime.Now;
            lockedDate = lockedDate.AddMinutes(10);
            var lockedDate2 = lockedDate.ToString("yyyy-MM-dd HH:mm");

            string sql = @"Update booking set isCancelled = '" + cancelled + "'where bookingId = '" + bookingId + "'";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();

        }

        public static int AddRoute(Route r)
        {
            var startDate = r.StartDate.ToString("yyyy-MM-dd HH:mm:ss");
            var endDate = r.EndDate.ToString("yyyy-MM-dd HH:mm:ss");

            string sql = @"insert into Route(fromZip, fromAddress, toZip, toAddress, startDate, endDate) values ('" + r.FromZip + "','"+ r.FromAdress+ "','"+ r.ToZip +"','"+ r.ToAdress + "','"+ startDate + "','"+ endDate +"')";

            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();

            return getMaxRouteId();
    }

        public static int getMaxRouteId()
        {
            //string sql = @"SELECT max(routeId) FROM route";
            string sql = @"select max(routeId) as routeId from route";

            SqlCommand dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            int route;

            if (dbReader.Read())

                route = ((int)(dbReader["routeId"]));

            else

                route = 0;

            AccessDBSQLClient.Close();
            return route;
        }
        }
}
